Missouri’s childcare deserts in 2023, by Zip Code Tabulation Area
Finding #2: Number of children in childcare deserts
202,318 children of five and under across Missouri live in zip codes areas that are childcare deserts. This is almost half of the 442,254 children five and under
Population in childcare deserts
Total population
Finding #3: Pandemic changes in childcare
From 2019 to September 2020, the number of childcare programs decreased by 30%, leaving 80,000 more children in childcare deserts than the year before. During 2020, the number of children in childcare deserts was the highest it has reached in the last several years
The number of programs bounced back sharply by six months later though, and since 2021, the number of children in childcare deserts has steadily towards where it was pre-pandemic
Number of children in childcare deserts by month and year
Number of childcare programs by month and year
Finding #4: Age breakouts of for startup, expansion, or enhancement
Of programs that received CRRSA funding for startup, expansion, or enhancement, less than half as many slots were added for infant and toddlers as for preschool aged children
Finding #5: Funding data we recieved shows less money went to deserts
Of the total of 114,681,667 dollars in the data we received, less than a third - 25,757,599 - of it went to childcare deserts
Our 114,681,667 dollars is about half of the over 230,000,00 DESE has to spend in total through the several different funding sources
203 of 230 million has been awarded so far in total
It looks like 170 million in total has been awarded from the grants we received, which more recent data would likely show. Our few months lag is the reason we don’t have all of that
All money in data sent us from DESE
Current numbers for our requests in data sent us from DESE
Source Code
---title: "Findings: Disappearing Daycare"execute: output: false echo: falseformat: html: code-tools: true theme: cosmo toc: true---```{r}library(tidyverse)library(janitor)library(readxl)library(tidycensus)library(here)library(sf)library(lubridate)library(tmap)library(DT)# Cache the zip code tabulation data we want from the Census options(tigris_use_cache =TRUE)# Sets tmap to interactive modetmap_mode("view")```# Finding #1: Map - Missouri's childcare deserts in 2023, by Zip Code Tabulation Area```{r}# LOAD DATA# Load clean and crosswalked data for all zctas from ChildCareAware surveychildcare_aware <-read_csv(here("data", "processed", "ChildCareAware_cleaned_and_crosswalked.csv")) %>%mutate(zcta =as.character(zcta)) # Load clean and crosswalked data for all zctas from ChildCareAware survey, with all other zctas and populations baked in childcare_supply_and_demand_2019_2023 <-read_csv(here("data", "processed", "childcare_supply_and_demand_2019_2023.csv")) %>%mutate(zcta =as.character(zcta))# Load all zctas not included in ChildCareAware survey # There are 1023 unique zctas in MO according to these crosswalkmo_zctas <-read_excel(here("data", "raw", "ZIPCodetoZCTACrosswalk2021UDS.xlsx"), sheet =1) %>%clean_names() %>%filter(state =="MO")# 5 year ACS estimates pf population five and under for all zctas in the countryacs <-read_csv(here("data", "processed", "pop_zcta_5acs.csv"))crrsa_startup_expansion <-read_excel(here("data","raw", "CRRSA Startup and Expansion with Paid.xlsx")) %>%clean_names()# Load all relief data with cleaned zips and years for when and where money was dispersed to relief_funding <-read_csv(here("data", "processed", "all_relief_cleaned.csv")) %>%mutate(zip =as.character(zip))# Join crosswalk with relief funding zips to that each zip matches a zcta to correspond to desert datarelief_zcta <- relief_funding %>%inner_join(mo_zctas, by =c("zip"="zip_code"))``````{r}supply_and_demand_2023 <- childcare_supply_and_demand_2019_2023 %>%filter(year =="2023") %>%mutate(ratio = pop_5_and_under / licensed_capacity) %>%mutate(status =case_when(ratio <3~"non-desert", ratio >=3& ratio <10~"desert", ratio >=10~"extreme", is.na(ratio) & pop_5_and_under >=50~"desert",is.na(ratio) & pop_5_and_under <50~"non-desert",TRUE~"non-desert"))map_df <- supply_and_demand_2023 %>%mutate(status_num =case_when(status =="non-desert"~0, status =="desert"~1, status =="extreme"~3)) %>%select(zcta, status, status_num, ratio)#write.csv(map_df, "datawrapper_map.csv")``````{r}# JOIN CROSSWAKLED DATA WITH GEOM FOR MAPPING # Now we can use zctas to get geoms for mapping # Get the 2017-2021 5-year ACS ZTCAs for the whole US so that we can join these with zip codes and get geoms for mapping# B09001_005 is a dummy variable, we just need the geoms from the callzcta_geoms <-get_acs(geography ="zip code tabulation area", variables ="B09001_005", geometry =TRUE) %>%select(GEOID, geometry)# Join the survey and non-survey data with geoms from Census for mapping # This join to zcta geoms from ACS data drops 5 values, but anti join shows they are all non-deserts and "point" zctas for PO boxes map_df_sf <- map_df %>%inner_join(zcta_geoms, by =c("zcta"="GEOID")) %>%st_as_sf(sf_column_name ="geometry")``````{r}#| output: true# MAP DATA tm_shape(map_df_sf) +tm_polygons("status_num",palette ="Reds",legend.show =FALSE)```# Finding #2: Number of children in childcare deserts- 202,318 children of five and under across Missouri live in zip codes areas that are childcare deserts. This is almost half of the 442,254 children five and under```{r}deserts_2023 <- supply_and_demand_2023 %>%filter(status %in%c("desert", "extreme")) %>%select(zcta, pop_5_and_under)# 202,318 in 227 zctas that are childcare desertssum(deserts_2023$pop_5_and_under, na.rm =TRUE)# Same five ZCTAs didn't join, but again, these are non-deserts PO boxes anywaymo_zctas_with_pop <- mo_zctas %>%left_join(acs, by =c("zcta"="GEOID")) %>%select(zcta, pop_5_and_under)# 442,254 children under 5 or under across the statesum(mo_zctas_with_pop$pop_5_and_under, na.rm=TRUE)percent_in_deserts <-202318/442254```### Population in childcare deserts ```{r}#| output: true#| column: pagedatatable(deserts_2023, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```### Total population```{r}#| output: true#| column: pagedatatable(mo_zctas_with_pop, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```# Finding #3: Pandemic changes in childcare - From 2019 to September 2020, the number of childcare programs decreased by 30%, leaving 80,000 more children in childcare deserts than the year before. During 2020, the number of children in childcare deserts was the highest it has reached in the last several years- The number of programs bounced back sharply by six months later though, and since 2021, the number of children in childcare deserts has steadily towards where it was pre-pandemic```{r}yearly_deserts <- childcare_supply_and_demand_2019_2023 %>%mutate(ratio = pop_5_and_under / licensed_capacity) %>%mutate(status =case_when(ratio <3~"non-desert", ratio >=3~"desert", is.na(ratio) & pop_5_and_under >=50~"desert",is.na(ratio) & pop_5_and_under <50~"non-desert",TRUE~"non-desert")) %>%filter(status =="desert") %>%group_by(year, month) %>%summarize(children_in_deserts =sum(pop_5_and_under))num_programs_yearly <- childcare_aware %>%group_by(year, month) %>%summarize(num_program =sum(childcare_programs))```## Number of children in childcare deserts by month and year```{r}#| output: true#| column: pagedatatable(yearly_deserts, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```## Number of childcare programs by month and year```{r}#| output: true#| column: pagedatatable(num_programs_yearly, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```# Finding #4: Age breakouts of for startup, expansion, or enhancement- Of programs that received CRRSA funding for startup, expansion, or enhancement, less than half as many slots were added for infant and toddlers as for preschool aged children```{r}age_breakouts <- crrsa_startup_expansion %>%select(number_of_infant_toddler_slots_adding, number_of_preschool_slots_adding, number_of_school_age_slots_adding) %>%mutate_all(~replace(., is.na(.), 0)) %>%pivot_longer(number_of_infant_toddler_slots_adding:number_of_school_age_slots_adding, names_to ="age_group", values_to ="number")%>%group_by(age_group) %>%summarize(number =sum(number))``````{r}#| output: true#| column: pagedatatable(age_breakouts, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```# Finding #5: Funding data we recieved shows less money went to deserts- Of the total of 114,681,667 dollars in the data we received, less than a third - 25,757,599 - of it went to childcare deserts- Our 114,681,667 dollars is about half of the over 230,000,00 DESE has to spend in total through the several different funding sources- 203 of 230 million has been awarded so far in total- It looks like 170 million in total has been awarded from the grants we received, which more recent data would likely show. Our few months lag is the reason we don't have all of that ```{r}# Filter the capacity deserts to data from 2021 and 2022 and remove non-desert zctas, and aggregate to yearlysupply_and_demand_2021_2022 <- childcare_supply_and_demand_2019_2023 %>%filter(year %in%c("2021", "2022")) %>%group_by(zcta, year) %>%summarize(pop_5_and_under =sum(pop_5_and_under), licensed_capacity =sum(licensed_capacity)) %>%mutate(ratio = pop_5_and_under/licensed_capacity) %>%mutate(status =case_when(ratio <3~"non-desert", ratio >=3~"desert", is.na(ratio) & pop_5_and_under >=50~"desert",is.na(ratio) & pop_5_and_under <50~"non-desert",TRUE~"non-desert")) # This join is for all funding together relief_to_desert_join <- relief_zcta %>%group_by(zcta, year) %>%summarize(total =sum(amount)) %>%left_join(supply_and_demand_2021_2022, by =c("zcta", "year")) ``````{r}# The big reveal total_desert_relief <- relief_to_desert_join %>%group_by(status) %>%summarize(total =sum(total))portion_to_deserts <-25757599/88924069# Check total here with total from raw dataset to make sure now data went missing on joins # 114681667 in raw data matches exact amount here +1 for rounded check_total <-25757599+88924069``````{r}#| output: true#| column: pagedatatable(total_desert_relief, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))``````{r}# This join is for funding my source relief_to_desert_type_join <- relief_zcta %>%group_by(zcta, year, type) %>%summarize(total =sum(amount)) %>%left_join(supply_and_demand_2021_2022, by =c("zcta", "year")) %>%mutate(status =case_when(is.na(ratio) ~"non-desert", TRUE~"desert")) %>%group_by(type, status) %>%summarize(total =sum(total, na.rm =TRUE)) %>%pivot_wider(names_from = status, values_from = total)#write.csv(relief_to_desert_type_join, "datwrapper_stacked_bars.csv")``````{r}#| output: true#| column: pagedatatable(relief_to_desert_type_join, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))``````{r}dese_funds <-read_excel(here("data", "processed", "dese_funds_update.xlsx")) %>%clean_names() %>%mutate(awarded_approved_amount =as.numeric(if_else(awarded_approved_amount =="Pending", "0", awarded_approved_amount))) %>%mutate(paid_amount_as_of_4_14_23 =as.numeric(paid_amount_as_of_4_14_23))our_foia_data <- dese_funds %>%filter(application_name %in%c("Enhancement", "Paycheck Protection", "Technical Business Assistance","Expansion", "Startup", "Retention of Staff", "Annual Training Costs"))foia_awarded <-sum(our_foia_data$awarded_approved_amount)foia_paid <-sum(our_foia_data$paid_amount_as_of_4_14_23)total_awarded <-sum(dese_funds$awarded_approved_amount)total_paid <-sum(dese_funds$paid_amount_as_of_4_14_23)our_portion <-114681667/233314660```## All money in data sent us from DESE ```{r}#| output: true#| column: pagedatatable(dese_funds, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```## Current numbers for our requests in data sent us from DESE ```{r}#| output: true#| column: pagedatatable(our_foia_data, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```